package com.wooboo.server.ext.service;

import java.util.Date;
import java.util.List;

import com.wooboo.server.ext.ApiError;
import com.wooboo.server.ext.constants.BaseAttribute;
import com.wooboo.server.ext.data.Ad;
import com.wooboo.server.ext.data.AdMonitor;
import com.wooboo.server.ext.data.ChargeOrder;
import com.wooboo.server.ext.data.PhoneTrafficGift;
import com.wooboo.server.ext.data.SysLogger;
import com.wooboo.server.ext.data.Traffic;
import com.wooboo.server.ext.data.TrafficGift;
import com.wooboo.server.ext.data.UserCompany;
import com.wooboo.server.ext.data.UserFinance;
import com.wooboo.server.ext.data.VerificationCode;
import com.wooboo.server.ext.dbutil.JdbcTemplate;
import com.wooboo.server.ext.dbutil.Sql;
import com.wooboo.server.ext.util.DateUtil;


public class DbService {
	private JdbcTemplate jdbcTemplate = new JdbcTemplate();

	/**
	 * 查找广告主信息
	 * */
	public UserCompany findCompanyById(Integer id) throws ApiError{
		Sql sql = new Sql("select * from user_company u where u.id=?");
		    sql.addParam(id);
		return jdbcTemplate.findObject(sql, UserCompany.class);
	}
	/**
	 * 查找广告主账户信息
	 */
	public UserFinance findUserFinanceById(Integer advertiserId) throws ApiError{
		Sql sql = new Sql("select * from `user_finance` u where u.`uid`=?");
		    sql.addParam(advertiserId);
		return jdbcTemplate.findObject(sql, UserFinance.class);
	}
	//流量汇注册完成后分配company_code,更新到数据库
	public void update4Register(String company_code,Integer id) throws ApiError{
		Sql sql = new Sql("update  `user_company` u set u.`company_code`=?,u.`api_status`=? where u.`id`=?");
		    sql.addParam(company_code,Traffic.API_STATUS_REGISTER,id);
		jdbcTemplate.updateObject(sql);
	}
	
	//流量汇已接入
    public void update4Apply(Integer id) throws ApiError{
		Sql sql = new Sql("update  `user_company` u set u.`api_status`=? where u.`id`=?");
		    sql.addParam(Traffic.API_STATUS_APPLY,id);
		jdbcTemplate.updateObject(sql);
	}
	
	//
    public ChargeOrder findOrderById(Integer id) throws ApiError{
		Sql sql = new Sql("select o.id,o.plan_id,o.to_uid,p.start_date,p.end_date,o.create_time,o.order_no,o.amount,o.msg,o.order_status,o.api_status,o.contact_account_no,o.traffic_m");
		    sql.append(" from user_recharge_order o,admanage_plan p where o.plan_id=p.id");
		    sql.append(" and o.id=?");
		    sql.addParam(id);
		return jdbcTemplate.findObject(sql, ChargeOrder.class);
	}
    
    
  //合同同步接口
    public void updateOrderOn(Integer status,Integer id,String contact_account_no) throws ApiError{
    	Sql sql = new Sql("update `user_recharge_order` u set u.`api_status`=?,u.`contact_account_no`=? where u.`id`=?");
    	    sql.addParam(status,contact_account_no,id);
    	jdbcTemplate.updateObject(sql);    
    }
    
    //合同同步接口
    public void updateOrderOn(Integer status,Integer id) throws ApiError{
    	Sql sql = new Sql("update `user_recharge_order` u set u.`api_status`=? where u.`id`=?");
    	    sql.addParam(status,id);
    	jdbcTemplate.updateObject(sql);    
    }
    //充值完成，更新广告合同编号到广告计划
    public void updateAdPlanOnContact(String contact_account_no,Integer id) throws ApiError{
    	Sql sql = new Sql("update `admanage_plan` p set p.`contact_account_no`=? where p.`id`=?");
    	    sql.addParam(contact_account_no,id);
    	    jdbcTemplate.updateObject(sql);    
    }
    
	
	public void saveLogger(SysLogger logger) throws ApiError{
		Sql sql = new Sql("INSERT INTO `sys_logger` (`business_id`, `business_code`, `operate_name`, `create_user`, `user_name`, `operate_desc`, `create_time`)");
	        sql.append("VALUES (?,?,?,?,?,?,?)");
	        sql.addParam(logger.business_id,logger.business_code,logger.operate_name,logger.create_user,logger.user_name,logger.operate_desc,logger.create_time);
	    jdbcTemplate.saveObject(sql);
	}
	
	
	public VerificationCode saveVerification(VerificationCode vcode) throws ApiError{
		Sql sql = new Sql("INSERT INTO phone_verification_code (phone_number, verification_code) VALUES (?,?)");
		    sql.addParam(vcode.phone_number,vcode.verification_code);
		    vcode.id=jdbcTemplate.saveObject(sql);
	    return vcode;	    
	}
	public Integer sendTimes(String phone_number,Date date) throws ApiError{
		Sql sql = new Sql("SELECT COUNT(1) FROM phone_verification_code vc WHERE vc.phone_number=?");
		    sql.addParam(phone_number);
		    sql.appendBetween("AND", "vc.send_time", DateUtil.get00HourDate(date), DateUtil.get24HourDate(date));
		return jdbcTemplate.findCount(sql); 
	}
	public VerificationCode findVerification(String phone_number) throws ApiError{
		Sql sql = new Sql("SELECT vc.* FROM phone_verification_code vc WHERE vc.phone_number=? ORDER BY vc.id DESC LIMIT 1");
	        sql.addParam(phone_number);
	    return jdbcTemplate.findObject(sql, VerificationCode.class);
	}
	public void updateVerificationOn(Integer id) throws ApiError{
		Sql sql = new Sql("UPDATE phone_verification_code vc SET vc.is_verified=? WHERE vc.id=?");
		    sql.addParam(BaseAttribute.LOGIC_TRUE,id);
		    jdbcTemplate.updateObject(sql);
	}
	
	// 查询验证码
	public VerificationCode findVerification(String phone_number, String code) throws ApiError {
		Sql sql = new Sql("SELECT vc.* FROM phone_verification_code vc WHERE vc.phone_number=? AND vc.verification_code=? ORDER BY vc.id DESC LIMIT 1");
		sql.addParam(phone_number);
		sql.addParam(code);
		return jdbcTemplate.findObject(sql, VerificationCode.class);
	}
	
	public Ad findAdInfoById(Integer id) throws ApiError{
		Sql sql = new Sql("select idea.*,p.owner advertiser_id,p.contact_account_no from admanage_idea idea,admanage_plan p where idea.plan_id=p.id and idea.id=? ");
		    sql.addParam(id);
		return jdbcTemplate.findObject(sql, Ad.class);
	}
	
	
	
	public PhoneTrafficGift findPhoneTrafficGiftBySequence(String sequence) throws ApiError{
		Sql sql = new Sql("select g.* from `phone_traffic_gift` g where g.`sequence`=?" );
		    sql.addParam(sequence);
	    return jdbcTemplate.findObject(sql, PhoneTrafficGift.class);	    
	}
	
	public PhoneTrafficGift findPhoneTrafficGiftByPhoneNumberAndId(Integer id,String phone_number) throws ApiError{
		Sql sql = new Sql("select g.* from `phone_traffic_gift` g where g.`plan_id`=? and g.`phone_number`=?");
		    sql.addParam(id,phone_number);
	    return jdbcTemplate.findObject(sql, PhoneTrafficGift.class);	    
	}
	
	public void savephoneTrafficGift(PhoneTrafficGift g) throws ApiError{
		Sql sql = new Sql("INSERT INTO `phone_traffic_gift` (`sequence`, `phone_number`, `product_id`, `product_value`, `priduct_price`, `plan_id`,`group_id`,`idea_id`, `advertiser_id`, `state`) ");
		    sql.append("VALUES (?,?,?,?,?,?,?,?)");
		    sql.addParam(g.sequence,g.phone_number,g.product_id,g.product_value,g.price,g.plan_id,g.group_id,g.iead_id,g.advertiser_id,g.state);
		    jdbcTemplate.updateObject(sql);
	}
	
	public List<TrafficGift> queryTrafficGift() throws ApiError{
		Sql sql = new Sql("select t.* from `sys_traffic_gift` t where t.`status`=?");
		    sql.addParam(BaseAttribute.LOGIC_TRUE);
	    return jdbcTemplate.queryForList(sql, TrafficGift.class);
	}
	
	public TrafficGift findTrafficGift(String code,Integer planId) throws ApiError{
		Sql sql = new Sql("select stg.`code`,stg.`name`,stg.`value`,apg.`price` from `sys_traffic_gift` stg,`admanage_plan_gift` apg where stg.`code`=apg.`code` and apg.`plan_id`=? and apg.`code`=? ");
		    sql.addParam(planId,code);
		return jdbcTemplate.findObject(sql, TrafficGift.class);
	}
	
	
	public List<TrafficGift> queryTrafficGiftByPlanId(Integer planId) throws ApiError{
		Sql sql = new Sql("select stg.`code`,stg.`name`,stg.`value`,apg.`price` from `sys_traffic_gift` stg,`admanage_plan_gift` apg where stg.`code`=apg.`code` and apg.`plan_id`=? ");
		    sql.addParam(planId);
		return jdbcTemplate.queryForList(sql, TrafficGift.class);
	}
	
	
	public TrafficGift findTrafficGift(String code) throws ApiError{
		Sql sql = new Sql("select * from `sys_traffic_gift` where `code`=? ");
		    sql.addParam(code);
		return jdbcTemplate.findObject(sql, TrafficGift.class);
	}
	
	
	public AdMonitor findIdeaMonitor(Integer ideaId) throws ApiError{
		Sql sql = new Sql("select p.id planId,g.id groupId ,idea.id ideaId,p.total_budget_rmb,p.total_budget_flow,g.group_budget_rmb,g.group_budget_flow,p.billing_type,idea.bid_price price");
		    sql.append(",ifnull(tmp.show,0) `show`,ifnull(tmp.click,0) `click`,ifnull(tmp.traffic,0) `traffic`,ifnull(tmp.money_ad,0) money_ad");
	        sql.append(" from `admanage_plan` p,`admanage_group` g,`admanage_idea` idea ");
	        sql.append(" left join (select r.idea_id,sum(r.show) `show`,sum(r.click) `click`,sum(r.traffic) `traffic`,sum(r.money_ad) `money_ad`  from `rep_idea_hour` r where r.idea_id=? group by r.idea_id) as tmp");
		    sql.append(" on idea.id=tmp.idea_id");
		    sql.append(" where idea.`group_id`=g.`id` and idea.`plan_id`=p.`id` and idea.id=?");
		    sql.addParam(ideaId,ideaId);
		    
		    AdMonitor am =  jdbcTemplate.findObject(sql, AdMonitor.class);
	        
	    Sql sql0 = new Sql("select r.`idea_id`,ifnull(sum(r.`show`),0) `todayShow`,ifnull(sum(r.`click`),0) `todayClick`,ifnull(sum(r.`traffic`),0) todayTraffic,ifnull(sum(r.`money_ad`),0) todayMoneyAd from `rep_idea_hour` r where ");  
	        sql0.append(" r.`idea_id`=? and r.`created_date`>=? group by r.`idea_id`");
	        sql0.addParam(ideaId,DateUtil.get00HourDate(new Date()));
	        AdMonitor today = jdbcTemplate.findObject(sql0, AdMonitor.class);
	        
	        if(null!=am&&null!=today){
	        	am.setTodayClick(today.getTodayClick());
	        	am.setTodayMoneyAd(today.getTodayMoneyAd());
	        	am.setTodayShow(today.getTodayShow());
	        	am.setTodayTraffic(today.getTodayTraffic());
	        }
	        
	     return am;   
	}
	
	
	public void saveAdmanagePlanLimit(AdMonitor am) throws ApiError{
		Sql sql = new Sql("INSERT INTO `admanage_plan_limit` (`plan_id`, `show`, `click`, `traffic_m`, `money_ad`)");
		    sql.append(" VALUES (?,?,?,?,?) ON DUPLICATE KEY UPDATE  `show`=?,`click`=?,`traffic_m`=?,`money_ad`=?");
		    sql.addParam(am.planId,am.show,am.click,am.traffic,am.money_ad,am.show,am.click,am.traffic,am.money_ad);
		    jdbcTemplate.updateObject(sql);
	}
	
	
	public void update4PhoneTrafficGiftSuccess(PhoneTrafficGift pg) throws ApiError{
			//账户中扣除相应金额
			Sql sql = new Sql("update `user_finance` uf set uf.`traffic_yuan`=uf.`traffic_yuan`-? where uf.`uid`=?");
			    sql.addParam(pg.getAdvertiser_id());
			    jdbcTemplate.updateObject(sql);
			//更新领取记录为成功
			Sql sql1 = new Sql("update  `phone_traffic_gift` ptg set ptg.`state`=?  where ptg.`sequence`=?");
			    sql1.addParam(BaseAttribute.TRAFFIC_GIFT_SUCCESS,pg.sequence);
			    jdbcTemplate.updateObject(sql1);
	}
	
	public void update4PhoneTrafficGiftFail(PhoneTrafficGift pg) throws ApiError{
		Sql sql1 = new Sql("update  `phone_traffic_gift` ptg set ptg.`state`=?  where ptg.`sequence`=?");
	    sql1.addParam(BaseAttribute.TRAFFIC_GIFT_FAIL,pg.sequence);
	    jdbcTemplate.updateObject(sql1);
	}
	
	
	
	
	
	
	
	
}
